package excel;
import java.io.IOException;

import android.util.Log;
import android.widget.Toast;

import com.independentsoft.office.spreadsheet.Cell;
import com.independentsoft.office.spreadsheet.Column;
import com.independentsoft.office.spreadsheet.DataBarColor;
import com.independentsoft.office.spreadsheet.Font;
import com.independentsoft.office.spreadsheet.MergedCell;
import com.independentsoft.office.spreadsheet.Workbook;
import com.independentsoft.office.spreadsheet.Worksheet;
import com.independentsoft.office.spreadsheet.styles.CellAlignment;
import com.independentsoft.office.spreadsheet.styles.CellFormat;
import com.independentsoft.office.spreadsheet.styles.HorizontalAlignment;
import com.independentsoft.office.spreadsheet.styles.VerticalAlignment;

/*
 * Sheet Number : start at 0
 * Row Number : start at 0
 * Cell : start at 1
 * 
 * Generalize start at 1
 * 
 */

enum enum_{
	HorizontalCenter, VerticalCenter, HorizontalLeft, HorizontalRight, Center,
	VerticalCenterRight, VerticalCenterLeft
};

enum enum_font{
	FONT_OPTION_BOLD, FONT_OPTION_ITALIC, FONT_OPTION_BOLD_ITALIC
};


/**
 * @author Progistar
 * @version 1.0.3
 * @since 2013-11-28
 * @see under shFontSet
 */
public class ShWorkbook {
	private Workbook workbook;
	private final int MAXSHEETS = 100; //the number of maximum sheets
	public static final String HORIZONTAL_CENTER = enum_.HorizontalCenter.toString();
	public static final String VERTICAL_CENTER = enum_.VerticalCenter.toString();
	public static final String HORIZONTAL_LEFT = enum_.HorizontalLeft.toString();
	public static final String HORIZONTAL_RIGHT = enum_.HorizontalRight.toString();
	public static final String CENTER = enum_.Center.toString();
	public static final String VERTICAL_CENTER_LEFT = enum_.VerticalCenterLeft.toString();
	public static final String VERTICAL_CENTER_RIGHT = enum_.VerticalCenterRight.toString();
	public static final String FONT_OPTION_BOLD = enum_font.FONT_OPTION_BOLD.toString();
	public static final String FONT_OPTION_ITALIC = enum_font.FONT_OPTION_ITALIC.toString();
	public static final String FONT_OPTION_BOLD_ITALIC = enum_font.FONT_OPTION_BOLD_ITALIC.toString();
	public static final int DEFAULT_FONT_SIZE = 9;
	
	//create workbook
	//create default sheet
	/**
	 * ::Workbook Constructor
	 * Initializes ShWorkbook object
	 * Plus, creates default new sheet
	 */
	public ShWorkbook(){
		workbook = new Workbook();
		shAddSheet("ab",30,20); //Row : Column = 5 : 1
		//default: create first sheet
	}
	
	//create sheet
	/**
	 * ::Sheet Method
	 * @param sheetName
	 * @param defaultRowHeight
	 * @param defaultColumnWidth
	 */
	public void shAddSheet(String sheetName, double defaultRowHeight, double defaultColumnWidth){
		Worksheet worksheet = new Worksheet(sheetName);
		worksheet.setDefaultRowHeight(defaultRowHeight);
		worksheet.setDefaultColumnWidth(defaultColumnWidth);
		workbook.getSheets().add(worksheet);
		
	}
	
	//Save workbook given path
	/**
	 * ::Workbook Method
	 * @param filePath Absolute path for saving file
	 * @return <b> boolean <br>true:: Correct filePath<br>false:: InCorrect filePath or null 
	 */
	public boolean shSaveWorkbook(String filePath){
		if(filePath != null){
			try {
				workbook.save(filePath);
			} catch (IOException e) {
				e.printStackTrace();
				Log.e("h","here");
				return false;
			}
			return true;
		}return false;
	}

	//Check sheet
	/**
	 * @param sheetNumber 
	 * @return <b>boolean<br>true:: 0<= sheetNumber< MAXSHEETS<br>false:: others
	 */
	private boolean shCheckSheet(int sheetNumber){
		Worksheet worksheet;
		if(sheetNumber < 0){
			return false;
		}
		
		if(MAXSHEETS> sheetNumber){
			if(workbook.getSheets().get(sheetNumber)==null){
				worksheet = new Worksheet();
				workbook.getSheets().add(sheetNumber, worksheet);
			}
			return true;
		}else{
			return false;
		}
		
	}
	
	//Check values
	/**
	 * @param number
	 * @return <b>boolean<br>true:: 0<= number<br>false:: others
	 */
	private boolean shCheckRangeNegative(double number){
		if(number < 0 )
			return false;
		else
			return true;
	}
	
	//Insert cell values into sheet
	//first location start at 1
	/**
	 * @param sheetNumber 1<= sheetNumber < MAXSHEETS
	 * @param row 1<= row (it indicates that 1 is the first row)
	 * @param column 1<= column (it indicates that 1 is first column)
	 * @param content cell content (possible null)
	 * @param cellFormat to get cellFormat, use shFontSet methods (possible null)
	 * @return <b>boolean <br>true:: no exception <br>false:: exception by shCheckSheet and shCheckRangeNegative
	 */
	public boolean shInsertCell(int sheetNumber, int row, int column, String content, CellFormat cellFormat){
		if((!shCheckSheet(sheetNumber-1)) || (!shCheckRangeNegative(row)) || (!shCheckRangeNegative(column)))
			return false;
		if(content == null)
			content = "";
		

		//Cell setting
		Cell cell = new Cell(content);
		if(cellFormat != null)
			cell.setFormat(cellFormat);
		
		
		
		((Worksheet)workbook.getSheets().get(sheetNumber-1)).set(column,row,cell);
		
		
		return true;
	}
	
	//Set cell format
	//All parameter possible (null, null, 0) --> default option
	//fontOption: bold, italic and bold_italic
	//fontColor: red, yellow and ######
	//fontSize: the size of font integer
	/**
	 * @param fontOption Options are bold, italic, and bold_italic (possible null)
	 * @param fontColor red, yellow or FFFFFF notation (possible null)
	 * @param fontSize size of font. if fontSize 0, then applied DEFAULT_FONT_SIZE (possible 0)
	 * @return <b>CellFormat<br>if exception occurs, then return null 
	 */
	public CellFormat shFontSet(String fontOption, String fontColor, int fontSize){
		
		
		if(!shCheckRangeNegative(fontSize))
			return null;
		
		
		DataBarColor DBC = new DataBarColor();
		Font font = new Font();
		CellFormat cellFormat = new CellFormat(); 		
		
		//Set fontColor
		if (fontColor != null) {
			if (fontColor.equalsIgnoreCase("red")) {
				DBC.setColor("FF0000");
			} else if (fontColor.equalsIgnoreCase("yellow")) {
				DBC.setColor("FFF000");
			} else {
				DBC.setColor(fontColor);
			}
			font.setColor(DBC);
		}
		
		if(fontSize == 0)
			font.setSize(DEFAULT_FONT_SIZE);
		else
			font.setSize(fontSize);
		if(fontOption == null){
			fontOption = "default";
		} else {
			if (fontOption.equalsIgnoreCase(enum_font.FONT_OPTION_BOLD.name())) {
				font.setBold(true);
			} else if (fontOption.equalsIgnoreCase(enum_font.FONT_OPTION_ITALIC.name())) {
				font.setItalic(true);
			} else if (fontOption.equalsIgnoreCase(enum_font.FONT_OPTION_BOLD_ITALIC.name())) {
				font.setBold(true);
				font.setItalic(true);
			}
		}
		
		font.setName(fontOption);
		font.setFamily(2);
		
		cellFormat.setFont(font);

		return cellFormat;
	}

	//Set cell format with alignment
	/**
	 * @param fontOption Options are bold, italic, and bold_italic. (possible null)
	 * @param fontColor red, yellow or FFFFFF notation (possible null)
	 * @param fontSize size of font. if fontSize 0, then applied DEFAULT_FONT_SIZE (possible 0)
	 * @param align aligns are alignment static final variables. To get: ShWorkbook.~ (possible null)
	 * @return <b>CellFormat<br>if exception occurs, then return null 
	 */
	public CellFormat shFontSet(String fontOption, String fontColor, int fontSize, String align){
		CellFormat cellFormat = shFontSet(fontOption, fontColor, fontSize);
		if(align == null){
			return cellFormat;
		}
		
		CellAlignment alignment = new CellAlignment();

		if(align.equalsIgnoreCase(enum_.HorizontalCenter.name())){
			alignment.setHorizontalAlignment(HorizontalAlignment.CENTER);
		}else if(align.equalsIgnoreCase(enum_.VerticalCenter.name())){
			alignment.setVerticalAlignment(VerticalAlignment.CENTER);
		}else if(align.equalsIgnoreCase(enum_.HorizontalLeft.name())){
			alignment.setHorizontalAlignment(HorizontalAlignment.LEFT);
		}else if(align.equalsIgnoreCase(enum_.HorizontalRight.name())){
			alignment.setHorizontalAlignment(HorizontalAlignment.RIGHT);
		}else if(align.equalsIgnoreCase(enum_.Center.name())){
			alignment.setHorizontalAlignment(HorizontalAlignment.CENTER);
			alignment.setVerticalAlignment(VerticalAlignment.CENTER);
		}else if(align.equalsIgnoreCase(enum_.VerticalCenterLeft.name())){
			alignment.setVerticalAlignment(VerticalAlignment.CENTER);
			alignment.setHorizontalAlignment(HorizontalAlignment.LEFT);
		}else if(align.equalsIgnoreCase(enum_.VerticalCenterRight.name())){
			alignment.setVerticalAlignment(VerticalAlignment.CENTER);
			alignment.setHorizontalAlignment(HorizontalAlignment.RIGHT);
		}else{
			return cellFormat;
		}
		
		cellFormat.setAlignment(alignment);
		
		return cellFormat;
	}

	
	//Set Column info to sheet
	//first location start at 1
	/**
	 * @param sheetNumber 1<= sheetNumber < MAXSHEETS
	 * @param firstColumn 1<= firstColumn (it indicates that 1 is first column)
	 * @param lastColumn firstColumn <= lastColumn<br> Range: firstColumn ~ lastColumn
	 * @param columnWidth 0< columnWidth
	 * @return <b>boolean<br>true:: no exception<br>false:: exception by shCheckSheet, shCheckRangeNegative, and firstColumn > lastColumn
	 */
	public boolean shSetColumnWidth(int sheetNumber, int firstColumn, int lastColumn, double columnWidth){
		if((!shCheckSheet(sheetNumber-1)) || (!shCheckRangeNegative(firstColumn)) ||
				(!shCheckRangeNegative(lastColumn)) || (!shCheckRangeNegative(columnWidth)))
			return false;
		if(firstColumn > lastColumn)
			return false;
		
		Column column = new Column();
		
		column.setFirstColumn(firstColumn);
		column.setLastColumn(lastColumn);
		column.setWidth(columnWidth);
		
		
		((Worksheet)workbook.getSheets().get(sheetNumber-1)).getColumns().add(column);
		
		return true;
	}

	//Set Row info to sheet
	//first location start at 1
	/**
	 * @param sheetNumber 1<= sheetNumber< MAXSHEETS
	 * @param rowNumber 1<= rowNumber (it indicates that 1 is first row)
	 * @param height want to apply height to rowNumber row. 0< height
	 * @return <b> boolean <br>true:: no exception<br>false::  exception by shCheckSheet and shCheckRangeNegative
	 */
	public boolean shSetRowHeight(int sheetNumber, int rowNumber, double height){
		if((!shCheckSheet(sheetNumber-1)) || (!shCheckRangeNegative(rowNumber-1)) || (!shCheckRangeNegative(height)))
			return false;
		
		((Worksheet)workbook.getSheets().get(sheetNumber-1)).getRows().get(rowNumber-1).setHeight(height);
		((Worksheet)workbook.getSheets().get(sheetNumber-1)).getRows().get(rowNumber-1).setCustomHeight(true);
		return true;
	}
	
	//Merge cell
	/**
	 * @param sheetNumber 1<= sheetNumber< MAXSHEETS
	 * @param Xn the start range. EX> if (1,1) -> Xn="A1"
	 * @param Ym the end range. EX> if (3,4) -> Ym="D3"<br>Example: shMergeCell(1, "A8", "D1");
	 * @return<b> boolean<br>true:: no exception <br>false:: exception by shCheckSheet and Xn or Yn equals null
	 */
	public boolean shMergeCell(int sheetNumber, String Xn, String Ym){
		if(!shCheckSheet(sheetNumber-1))
			return false;
		if(Xn == null || Ym == null)
			return false;
		//Need: Exception about Xn, and Ym Range
		
		String RANGE = Xn+":"+Ym; //Ex> Xn:A1, Ym:B3
		MergedCell mergedCell = new MergedCell(RANGE); //Ex> A1:B3 Merged! 
        ((Worksheet)workbook.getSheets().get(sheetNumber-1)).getMergedCells().add(mergedCell);
        
		return true;
	}

	
}
